/**
 * 客户档案相关功能
 */
import { mysql } from 'src-ssr/db/connect';
import TableHelper from 'src-ssr/db/tableHelper';
import * as tools from 'src-ssr/util/common';
import logs from 'src-ssr/util/logs';
import { Sequelize, Op, QueryTypes } from 'sequelize';
import models, { sequelize } from 'src-ssr/db/sequelize';
import moment from 'moment';
import autocode from 'src-ssr/util/autocode';

import * as singleUtils from 'src-ssr/controller/common/singleOperate';
import * as mdUtils from 'src-ssr/controller/common/masterDetailOperate';

import listConfig from 'app/search/tenant/customer/list.js';
import summaryConfig from 'app/search/tenant/customer/summary.js';

const menuName = 'customer';
const modelName = 't_customers';
const modelLabel = '客户档案';

// 取数据
async function fetchData ({ startRow, count, filter, sortBy, descending, user }) {
  const where = await tools.assemblyWhere({
    filter,
    searchConfig: listConfig.search,
    fn: ({ where, field, search }) => {
    }
  });
  where.tenant_id = user.tid; // 只取该租户的数据

  const exclude = ['uid', 'history', 'images'];
  // 只取单条数据
  if (filter && filter.id) {
    where.id = filter.id;
    exclude.splice(exclude.indexOf('history'), 1);
    exclude.splice(exclude.indexOf('images'), 1);
  }

  const order = sortBy ? [[sortBy, descending ? 'DESC' : 'ASC']] : [];

  let right = { data: true };
  if (user) {
    right = await tools.checkUserRight(user, menuName, ['data']);
    if (!right.data) { // 是否有查看全部的权限
      where.create_user_id = user.id;
    }
  }

  let ret = { rows: [], count: 0 };
  try {
    ret = await models[modelName].findAndCountAll({
      attributes: {
        exclude
      },
      where,
      order,
      offset: count ? startRow : undefined,
      limit: count || undefined,
      group: count ? `${modelName}.id` : undefined
    });
  } catch (e) {
    console.error(modelLabel + '列表出错', e);
  }

  // 处理其它数据
  const rows = [];
  ret.rows.forEach(row => {
    const jsonRow = row.toJSON();

    if (user) jsonRow._owner = row.create_user_id == user.id; // 是否为拥有者
    rows.push(jsonRow);
  });

  if (filter && filter.id) {
    if (rows.length > 0) {
      return rows[0];
    } else {
      return {};
    }
  } else {
    return {
      data: rows,
      rowsNumber: typeof ret.count === 'object' ? ret.count.length : ret.count
    }
  }
}

// 列表
export const list = async (req, res) => {
  const { startRow, count, filter, sortBy, descending } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const ret = await fetchData({ startRow, count, filter, sortBy, descending, user });
  res.json(ret);
};

// 详细信息
export const detail = async (req, res) => {
  const { id } = req.body;

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const ret = await fetchData({ filter: { id }, user });

  res.json(ret);
};

// 简单信息
export const simple = async (req, res) => {
  const { uid } = req.query;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const ret = await models[modelName].findOne({
    attributes: ['nickname', 'company', 'contact', 'tel', 'address'],
    where: { uid: tools.uuidToBin(uid), tenant_id: user.tid }
  });

  res.json(ret || {});
};
/**
 * 新增
 */
export const create = async (req, res) => {
  const ret = await singleUtils.createData(modelName, modelLabel, menuName, req, true);

  res.json(ret);
};

/**
 * 更新
 */
export const update = async (req, res) => {
  const ret = await singleUtils.updateData(modelName, modelLabel, menuName, req, true);
  res.json(ret);
};

/**
 * 删除产品
 */
export const destroy = async (req, res) => {
  // 删除前置检查
  const checkModels = [
    async (id) => {
      const [rows] = await mysql.query(`SELECT bill_no
        FROM t_sales
        WHERE customer_uid=(
          SELECT uid
          FROM t_customers
          WHERE id = ?
        )`, [id]);
      if (rows.length > 0) {
        return rows.map(v => `已用于销售单${v.bill_no}，不能删除`);
      } else {
        return null;
      }
    },
    async (id) => {
      const [rows] = await mysql.query(`SELECT bill_no
        FROM t_devices
        WHERE customer_uid=(
          SELECT uid
          FROM t_customers
          WHERE id = ?
        )`, [id]);
      if (rows.length > 0) {
        return rows.map(v => `已用于设备档案${v.bill_no}，不能删除`);
      } else {
        return null;
      }
    }
  ];

  const checkResult = [];
  for (const fn of checkModels) {
    const msg = await fn(req.body.id);
    msg && checkResult.push(...msg);
  }
  if (checkResult.length > 0) {
    return res.json({ result: 'fail', message: checkResult.join('<br>') });
  }

  const ret = await singleUtils.destroyData(modelName, modelLabel, menuName, req, true);

  res.json(ret);
};

// 统计
export const summary = async (req, res) => {
  const { startRow, count, filter, sortBy, descending, columns } = req.body;

  const where = await tools.assemblyWhere({ filter, searchConfig: summaryConfig.search, raw: true });

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const right = await tools.checkUserRight(user, menuName, ['data']);
  if (!right.data) { // 是否有查看全部的权限
    where.push(`create_user_id=${user.id}`);
  }
  where.push(`tenant_id=${user.tid}`);

  const order = [];
  if (['year', 'month', 'day'].includes(sortBy)) { // 日期型的一起排序
    if (columns.includes('year')) {
      order.push(`year ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('month')) {
      order.push(`month ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('day')) {
      order.push(`day ${descending ? 'DESC' : 'ASC'}`);
    }
  } else {
    sortBy && columns.includes(sortBy) && order.push(`${sortBy} ${descending ? 'DESC' : 'ASC'}`);
  }

  const limit = count ? `LIMIT ${startRow}, ${count}` : '';

  // group by 字段
  const groupField = columns.filter(v => ['year', 'month', 'day', 'create_user_name', 'nickname'].includes(v)).join(', ');

  const allDataSql = `SELECT @idx:=@idx+1 AS id, ${groupField ? groupField + ',' : ''}
    COUNT(*) bill_count, SUM(sale_count) sale_count, SUM(sale_money) sale_money
    FROM (
      SELECT FROM_UNIXTIME(c.create_time, '%Y') year,
      FROM_UNIXTIME(c.create_time, '%m') month, FROM_UNIXTIME(c.create_time, '%d') day,
      c.create_user_name, c.nickname,
      (SELECT COUNT(DISTINCT m.uid) FROM t_sale_details d LEFT JOIN t_sales m ON d.master_uid=m.id WHERE c.uid=m.customer_uid) sale_count,
      (SELECT SUM(d.money) FROM t_sale_details d LEFT JOIN t_sales m ON d.master_uid=m.uid WHERE c.uid=m.customer_uid) sale_money,
      (SELECT COUNT(*) FROM t_devices d WHERE d.customer_uid=c.uid) device_count
      FROM t_customers c
      ${where.length > 0 ? ' WHERE ' + where.join(' AND ') : ''}
    ) a, (SELECT @idx:=0) p
    GROUP BY ${groupField || ''}`;

  const [stat] = await mysql.query(`SELECT COUNT(*) cnt,
    IFNULL(SUM(bill_count), 0) bill_count,
    IFNULL(SUM(sale_count), 0) sale_count,
    IFNULL(SUM(sale_money), 0) sale_money,
    IFNULL(SUM(device_count), 0) device_count
    FROM (${allDataSql}) a`);

  let rows = [];
  const sql = `${allDataSql}
    ${order.length > 0 ? ' ORDER BY ' + order.join(',') : ''}
    ${limit}`;
  try {
    [rows] = await mysql.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  let bill_count = 0, sale_count = 0, sale_money = 0, device_count = 0;
  rows.forEach(row => {
    row.bill_count = Number(row.bill_count);
    row.sale_count = Number(row.sale_count);
    row.sale_money = Number(row.sale_money);
    row.device_count = Number(row.device_count);

    bill_count += row.bill_count;
    sale_count += row.sale_count;
    sale_money += row.sale_money;
    device_count += row.device_count;
  });

  res.json({
    data: rows,
    rowsNumber: stat[0].cnt,
    summary: {
      total: {
        bill_count: Number(stat[0].bill_count),
        sale_count: Number(stat[0].sale_count),
        sale_money: Number(stat[0].sale_money),
        device_count: Number(stat[0].device_count)
      },
      page: {
        bill_count,
        sale_count,
        sale_money,
        device_count
      }
    }
  });
};
